alter table Contact
add Pin varchar(50) null
go
CREATE TABLE [dbo].[TravelPackage](
	[PackageId] [int] IDENTITY(1,1) NOT NULL,
	[Cost] [money] NOT NULL,
	[PackageName] [varchar](100) NOT NULL,
	[VacationLength] [varchar](50) NULL,
 CONSTRAINT [PK_TravelPackage] PRIMARY KEY CLUSTERED 
(
	[PackageId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[TravelPackage] ADD  CONSTRAINT [DF_TravelPackage_Cost]  DEFAULT ((0)) FOR [Cost]
GO
CREATE TABLE [dbo].[VacationOption](
	[OptionId] [int] IDENTITY(1,1) NOT NULL,
	[OptionName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_VacationOption] PRIMARY KEY CLUSTERED 
(
	[OptionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[PackageDestination](
	[PkgDestId] [int] IDENTITY(1,1) NOT NULL,
	[DestinationName] [varchar](100) NOT NULL,
	[PackageId] [int] NOT NULL,
 CONSTRAINT [PK_PackageDestination] PRIMARY KEY CLUSTERED 
(
	[PkgDestId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PackageDestination]  WITH CHECK ADD  CONSTRAINT [FK_PackageDestination_TravelPackage] FOREIGN KEY([PackageId])
REFERENCES [dbo].[TravelPackage] ([PackageId])
GO
ALTER TABLE [dbo].[PackageDestination] CHECK CONSTRAINT [FK_PackageDestination_TravelPackage]
GO
CREATE TABLE [dbo].[PackageDestinationOption](
	[PkgDestId] [int] NOT NULL,
	[OptionId] [int] NOT NULL,
 CONSTRAINT [PK_PackageDestinationOption] PRIMARY KEY CLUSTERED 
(
	[PkgDestId] ASC,
	[OptionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PackageDestinationOption]  WITH CHECK ADD  CONSTRAINT [FK_PackageDestinationOption_PackageDestination] FOREIGN KEY([PkgDestId])
REFERENCES [dbo].[PackageDestination] ([PkgDestId])
GO
ALTER TABLE [dbo].[PackageDestinationOption] CHECK CONSTRAINT [FK_PackageDestinationOption_PackageDestination]
GO
ALTER TABLE [dbo].[PackageDestinationOption]  WITH CHECK ADD  CONSTRAINT [FK_PackageDestinationOption_VacationOption] FOREIGN KEY([OptionId])
REFERENCES [dbo].[VacationOption] ([OptionId])
GO
ALTER TABLE [dbo].[PackageDestinationOption] CHECK CONSTRAINT [FK_PackageDestinationOption_VacationOption]
GO
CREATE TABLE [dbo].[OptionChoice](
	[ChoiceId] [int] NOT NULL,
	[PkgDestId] [int] NOT NULL,
	[OptionId] [int] NOT NULL,
	[ChoiceDescription] [varchar](100) NOT NULL,
 CONSTRAINT [PK_OptionChoice] PRIMARY KEY CLUSTERED 
(
	[ChoiceId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[OptionChoice]  WITH CHECK ADD  CONSTRAINT [FK_OptionChoice_PackageDestinationOption] FOREIGN KEY([PkgDestId], [OptionId])
REFERENCES [dbo].[PackageDestinationOption] ([PkgDestId], [OptionId])
GO
ALTER TABLE [dbo].[OptionChoice] CHECK CONSTRAINT [FK_OptionChoice_PackageDestinationOption]
GO
CREATE TABLE [dbo].[ClientPackage](
	[TravelId] [int] NOT NULL,
	[PkgDestId] [int] NOT NULL,
	[ClientId] [int] NOT NULL,
	[Status] [varchar](50) NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[ExpDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ClientPackage] PRIMARY KEY CLUSTERED 
(
	[TravelId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ClientPackage]  WITH CHECK ADD  CONSTRAINT [FK_ClientPackage_Contact] FOREIGN KEY([ClientId])
REFERENCES [dbo].[Contact] ([ContactId])
GO
ALTER TABLE [dbo].[ClientPackage] CHECK CONSTRAINT [FK_ClientPackage_Contact]
GO
ALTER TABLE [dbo].[ClientPackage]  WITH CHECK ADD  CONSTRAINT [FK_ClientPackage_PackageDestination] FOREIGN KEY([PkgDestId])
REFERENCES [dbo].[PackageDestination] ([PkgDestId])
GO
ALTER TABLE [dbo].[ClientPackage] CHECK CONSTRAINT [FK_ClientPackage_PackageDestination]
GO
CREATE TABLE [dbo].[ClientPackageChoice](
	[TravelId] [int] NOT NULL,
	[ChoiceId] [int] NOT NULL,
 CONSTRAINT [PK_ClientPackageChoice] PRIMARY KEY CLUSTERED 
(
	[TravelId] ASC,
	[ChoiceId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ClientPackageChoice]  WITH CHECK ADD  CONSTRAINT [FK_ClientPackageChoice_ClientPackage] FOREIGN KEY([TravelId])
REFERENCES [dbo].[ClientPackage] ([TravelId])
GO
ALTER TABLE [dbo].[ClientPackageChoice] CHECK CONSTRAINT [FK_ClientPackageChoice_ClientPackage]
GO
ALTER TABLE [dbo].[ClientPackageChoice]  WITH CHECK ADD  CONSTRAINT [FK_ClientPackageChoice_OptionChoice] FOREIGN KEY([ChoiceId])
REFERENCES [dbo].[OptionChoice] ([ChoiceId])
GO
ALTER TABLE [dbo].[ClientPackageChoice] CHECK CONSTRAINT [FK_ClientPackageChoice_OptionChoice]
GO
